/**
 * 
 */
package com.lb.store.hsqldb;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * SimpleExample.java. 2011-11-26下午4:24:50 @author LionBule.
 */
public class SimpleExample {

    /**
     * @param args
     * @throws SQLException
     */
    public static void main(String[] args) throws SQLException {
        String dbdir = "/home/hsqldb/testdb";
        checkDir(dbdir);
        try {
            Class.forName("org.hsqldb.jdbcDriver");
        } catch (Exception e) {
            System.out.println("ERROR: failed to load HSQLDB JDBC driver.");
            e.printStackTrace();
            return;
        }

        Connection conn = DriverManager.getConnection("jdbc:hsqldb:file:" + dbdir
                + "/testdb;shutdown=true", "sa", "");

        //create table;
        //createTable(conn);

        //insert data
        UserDO userDO = new UserDO();
        userDO.setName("lionbule");
        insertData(conn, userDO);

        //query
        List<UserDO> tempUserDO = queryUser(conn);
        System.out.println(tempUserDO);

        conn.close();
    }

    static void createTable(Connection conn) throws SQLException {
        //CREATE TABLE t (id INT PRIMARY KEY, scores INT ARRAY DEFAULT ARRAY[], names VARCHAR(20) ARRAY[10])
        StringBuilder tableSql = new StringBuilder();
        tableSql.append("DROP TABLE mytable IF EXISTS;");
        tableSql.append("CREATE TABLE mytable (id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY, name VARCHAR(20));");

        Statement stmt = conn.createStatement();
        stmt.executeUpdate(tableSql.toString());

        stmt.close();
    }

    static void insertData(Connection conn, UserDO user) throws SQLException {
        StringBuilder insertSql = new StringBuilder();
        insertSql.append("INSERT INTO mytable (name) values (?)");

        PreparedStatement pstmt = conn.prepareStatement(insertSql.toString());
        pstmt.setString(1, user.getName());
        pstmt.executeUpdate();
        pstmt.close();
    }

    static List<UserDO> queryUser(Connection conn) throws SQLException {
        StringBuilder querySql = new StringBuilder();
        querySql.append("SELECT id, name FROM mytable");

        PreparedStatement pstmt = conn.prepareStatement(querySql.toString(),
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = pstmt.executeQuery();

        List<UserDO> result = new ArrayList<UserDO>();
        UserDO userDO = null;
        while (rs.next()) {
            userDO = new UserDO();
            userDO.setId(rs.getInt("id"));
            userDO.setName(rs.getString("name"));
            result.add(userDO);
        }
        rs.close();
        pstmt.close();

        return result;
    }

    static void checkDir(String dir) {
        File file = new File(dir);
        if (!file.exists()) {
            file.mkdirs();
        }
    }

}

class UserDO {
    private int    id;
    private String name;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return id + ":" + name + "\n";
    }
}
